<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once('Entity/Doc_Documents_Entity.php');        
class Doc_Documents_Model extends Doc_Documents_Entity
{  
    public function __construct()
    {
        parent::__construct();
    }
    
    public function top_in_30day()
    {
        $limit = 6;
        $sql = "SELECT d.id, d.numpage, d.title, d.alias, d.image, c.alias as calias, m.title as mtitle, 
                c.title as ctitle, 
                c.alias as calias, FROM_UNIXTIME(d.created, '%Y-%m-%d') as created, d.hit, d.download
            FROM doc_documents d
            INNER JOIN doc_categories c ON c.id = d.cid
            INNER JOIN doc_menus m ON m.id = c.mid
            WHERE d.`status` = 1 
            	AND FROM_UNIXTIME(d.created, '%Y-%m-%d') >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
            ORDER BY d.hit DESC, d.download DESC
            LIMIT $limit";
        $query = $this->db->query($sql);
        return $query->result();
    }
    
    public function doclist_byTag($tagId)
    {
        $sql = "SELECT t.tags, d.id, d.numpage, d.title, d.alias, d.image, m.title as mtitle, m.alias as malias, m.order, m.id as mmid, 
                d.langcode, COUNT(did) AS numComment, l.flag, c.title as ctitle, c.id as cid, c.alias as calias, d.hit, d.download, d.created, d.createdby 
                FROM doc_documents d 
                INNER JOIN doc_categories c ON c.id = d.cid 
                INNER JOIN doc_menus m ON m.id = c.mid 
                LEFT OUTER JOIN doc_languages l ON l.langcode = d.langcode 
                LEFT OUTER JOIN doc_comments dc ON dc.did = d.id 
                INNER JOIN doc_tags_doc td ON td.doc_id = d.id
                INNER JOIN doc_tags t ON t.id = td.tag_id
                WHERE d.`status` = 1 AND t.id = ?
                GROUP BY d.id ORDER BY d.created DESC LIMIT 100 ";
        $query = $this->db->query($sql, array($tagId));
        return $query->result();
    }
    
    public function doclist($flag = 1, $cid = 0)
    {
        $sql = "SELECT d.id, d.numpage, d.title, d.alias, d.image, m.title as mtitle, m.alias as malias, m.order, 
                m.id as mmid, d.langcode,   COUNT(did) AS numComment, l.flag,
                c.title as ctitle, c.id as cid, c.alias as calias, d.hit, d.download, d.created, d.createdby
            FROM doc_documents d
            INNER JOIN doc_categories c ON c.id = d.cid
            INNER JOIN doc_menus m ON m.id = c.mid
            LEFT OUTER JOIN doc_languages l ON l.langcode = d.langcode
            LEFT OUTER JOIN doc_comments dc ON dc.did = d.id
            WHERE d.`status` = 1 ";
        if ($cid != 0) {
            $sql .= " AND  c.id IN($cid)";
        }
        $sql .= " GROUP BY d.id ";
        if ($flag == 1) {
            $sql .= " ORDER BY d.created DESC ";
        } elseif ($flag == 2) {
            $sql .= " ORDER BY d.hit DESC ";
        } elseif ($flag == 3) {
            $sql .= " ORDER BY d.download DESC ";
        }
        $sql .= " LIMIT 100 ";
        $query = $this->db->query($sql);
        return $query->result();     
    }
    
    public function updateHit($id = 0)
   {
        if (!isset($_SESSION['doc_documents'][$id])) {
            $_SESSION['doc_documents'][$id] = $id;
            $sql = "UPDATE doc_documents SET hit = hit + 1 WHERE id = ?";
            $this->db->query($sql, array($id));
        }
   }
    
    public function getIdByAlias($alias)
    {
        $sql = "SELECT id FROM doc_documents WHERE alias = ?";
        $query = $this->db->query($sql, array($alias));
        $obj = $query->row();
        if ($obj) {
            return $obj->id;
        } else {
            return 0;
        }
    }
    
    public function getDocumentByMenu($mid)
    {
        $sql = "SELECT d.*,  d.numpage, l.flag,
            	m.title as mtitle, c.title as ctitle, c.alias as calias, COUNT(did) AS numComment,
                c.id as cid, m.order, m.alias as malias
            FROM doc_documents d
            INNER JOIN doc_categories c ON c.id = d.cid
            INNER JOIN doc_menus m ON m.id = c.mid
            LEFT OUTER JOIN doc_languages l ON l.langcode = d.langcode
            LEFT OUTER JOIN doc_comments dc ON dc.did = d.id
            WHERE c.id = ? AND d.`status` = 1
            GROUP BY d.id
            ORDER BY d.created DESC";
        $query = $this->db->query($sql, array($mid));
        
        return $query->result();
    }
    
    public function getDocumentById($id)
    {
        $sql = "SELECT d.*, c.alias, d.numpage, l.flag,
            	m.title as mtitle, c.title as ctitle, c.alias as calias, COUNT(did) AS numComment,
                c.id as cid, m.order, m.alias as malias
            FROM doc_documents d
            INNER JOIN doc_categories c ON c.id = d.cid
            INNER JOIN doc_menus m ON m.id = c.mid
            LEFT OUTER JOIN doc_languages l ON l.langcode = d.langcode
            LEFT OUTER JOIN doc_comments dc ON dc.did = d.id
            WHERE d.id = ? AND d.`status` = 1
            GROUP BY d.id
            ORDER BY d.created DESC";
        $query = $this->db->query($sql, array($id));
        return $query->row();
    }
    
    public function getTagByDocId($docId)
    {
        $sql = "SELECT t.*
                FROM doc_documents d
                INNER JOIN doc_tags_doc td ON td.doc_id = d.id
                INNER JOIN doc_tags t ON t.id = td.tag_id
                WHERE d.id IN($docId) AND d.status = 1";
        $query = $this->db->query($sql);
        return $query->result();
    }
    
   public function ctotal($id)
    {
        $sql = "SELECT c.id
                FROM doc_comments c 
                INNER JOIN doc_documents a ON a.id = c.did
                WHERE a.id = ? AND a.`status` = 1 AND c.`status` = 1
            ";
        $query = $this->db->query($sql, array($id));
        return $query->num_rows();
    }
    
    
    public function getCommentById($id, $offset = 0)
    {
        $limit = 2;
        $sql = "SELECT c.*
                FROM doc_comments c 
                INNER JOIN doc_documents a ON a.id = c.did
                WHERE a.id = ? AND a.`status` = 1 AND c.`status` = 1
                LIMIT ?, $limit
                ";
        $query = $this->db->query($sql, array($id, $offset));
        return $query->result();
    }
    
    public function getlistCategory($mid)
    {
        $sql = "SELECT c.id FROM doc_categories c WHERE mid = ?";
        $query = $this->db->query($sql, array($mid));
        $arr = array();
        if ($query->num_rows() > 0) {
            foreach ($query->result() as $row) {
                $arr[] = $row->id;
            }
        }
        return $arr;
    }
    
    public function blockCourses($id = 0)
    {
        $f = root_dir().'libs/courses.xml';
        $data = simplexml_load_file($f);
        $arr = array();
        foreach ($data as $row) {
            if ($id == 0) {
                $arr[] = $row;
            } else if ($row->category == $id) {
                $arr[] = $row;
            }
        }
        return $arr;
    }
    
    public function getLastComment()
    {
        $sql = "SELECT d.id, d.title, d.alias, d.image, c.contents, c.fullname
            FROM doc_documents d
            INNER JOIN doc_comments c ON c.did = d.id
            WHERE d.status = 1 and c.status = 1
            GROUP BY d.id
            ORDER BY c.created DESC
            LIMIT 5";
        $query = $this->db->query($sql);
        return $query->result();
    }
    public function updateDownload($docId, $numDownload)
    {
        $this->setId($docId);
        $this->setDownload($numDownload);
        $this->save();
    }
    
    public function dataSort($field, $order)
    {
      $xOrder =  $order == 'asc' ? 'desc' : 'asc';
      $img = $order == 'desc' ? '<img src=\'/data/admin/down.jpg\' />' : ' <img src=\'/data/admin/up.jpg\' />';  
      $url = anchor('doc_documents/sort/'.$field.'/'.$xOrder, t($field).$img);
      $_SESSION['sm_sort_doc_documents'] = array('field' => $field, 'order' => $order, 'url' => $url);
    }
    
    public function dataIndex($target)
    {
      $numshow = 25;
      $this->db->select('doc_documents.id, doc_documents.title, doc_documents.created, doc_documents.status, doc_documents.hit, doc_categories.title as cid_type, doc_languages.title as langcode_type, doc_documents.download');
      $this->db->from('doc_documents');
      
        $this->db->join('doc_categories', 'doc_documents.cid = doc_categories.id');
        $this->db->join('doc_languages', 'doc_documents.langcode = doc_languages.langcode');
       
        if ($this->session->userdata('sm_filter_doc_categories'))
            $this->db->where('doc_documents.cid', $this->session->userdata('sm_filter_doc_categories')); 
        if ($this->session->userdata('sm_filter_doc_languages'))
            $this->db->where('doc_documents.langcode', $this->session->userdata('sm_filter_doc_languages'));
      if ($this->session->userdata('sm_find_doc_documents')) {
        $this->db->like('doc_documents.title', $this->session->userdata('sm_find_doc_documents'));
      }
      if (isset($_SESSION['sm_sort_doc_documents'])) {
        $sort = $_SESSION['sm_sort_doc_documents'];
        $this->db->order_by('doc_documents.'.$sort['field'], $sort['order']);
       }
      else {  
        $this->db->order_by('doc_documents.created', 'desc');
      }
      $this->db->limit($numshow, $target);
      
      $query = $this->db->get();
      $paging = paging_admin('doc_documents/index', $this->_total(), $numshow, 4);
      return array('query'=>$query, 'paging'=>$paging,'fil_doc_categories' => $this->Filter_doc_categories(), 'fil_doc_languages' => $this->Filter_doc_languages(),                    
                  'formFind' => $this->formFind());
    }
          
    private function cbo_doc_categories($id = 0)
    {
        $this->load->helper("form");
        $query = $this->db->get("doc_categories");
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $sl = $id == 0 ? set_value("cid") : $id;
        return form_dropdown("cid", $options, $sl);
    }
        
    private function cbo_doc_languages($id = '')
    {
        $this->load->helper("form");
        $query = $this->db->get("doc_languages");
        foreach ($query->result() as $row) {
            $options[$row->langcode] = $row->title;
        }
        $sl = $id == '' ? set_value("langcode") : $id;
        return form_dropdown("langcode", $options, $sl);
    }
    
    private function _total()
    {
        return $this->db->count_all_results("doc_documents"); 
    }
    
    private function _checkdoc_documentsById($id)
    {
        $query = $this->db->get_where("doc_documents", array("id" => $id));
        if ($query->num_rows() > 0) return true;
        else {
            notify_set_messages(notify_atten(t("Record không tồn tại")), "doc_documents");
            return false;
        }
    }
    
    private function formFind()
    {
        $this->load->helper('form');
        $attributes = array('name' => 'frmFind', 'id' => 'frmFind');
        $f = form_open(site_url('doc_documents/find'), $attributes);
        $data = array('name' => 'txt_find', 'id' => 'txt_find', 'value' => '', 'style' => 'width:200px');
        $f .= form_input($data);
        $s = array('name' => 'submit', 'value' => t('Find'), 'class' => 'ibutton green', 'style' => 'margin-left:10px');
        $f .= form_submit($s);
        if ($this->session->userdata('sm_find_doc_documents')) {
            $r = array('name' => 'btnReset', 'value' => 'Reset', 'class' => 'ibutton red', 'style' => 'margin-left:10px');
            $f .= form_submit($r);
        } 
        $f .= form_close();
        return $f;
    }
    
    private function Filter_doc_categories()
    {
        $this->load->helper('form');
        $query = $this->db->get('doc_categories');
        foreach ($query->result() as $row) {
            $options[$row->id] = $row->title;
        }
        $id = !$this->session->userdata('sm_filter_doc_categories') ? 0 : $this->session->userdata('sm_filter_doc_categories');
        $attributes = array('name' => 'frmFilterdoc_categories', 'id' => 'frmFilterdoc_categories');
        $f = form_open(site_url('doc_documents/filterdoc_categories'), $attributes);
        $f .= form_dropdown('cid', $options, $id);
        $data = array('name' => 'btnFilterdoc_categories', 'class' => 'ibutton green', 'style' => 'margin-left:10px', 'value' => t('Filter'));
        $f .= form_submit($data);
        if ($this->session->userdata('sm_filter_doc_categories')) {
            $r = array('name' => 'btnReset', 'class' => 'ibutton red', 'style' => 'margin-left:10px', 'value' => 'Reset');  
            $f .= form_submit($r); 
        }
        $f .= form_close();
        return $f;
    }
    
    private function Filter_doc_languages()
    {
        $this->load->helper('form');
        $query = $this->db->get('doc_languages');
        foreach ($query->result() as $row) {
            $options[$row->langcode] = $row->title;
        }
        $id = !$this->session->userdata('sm_filter_doc_languages') ? 0 : $this->session->userdata('sm_filter_doc_languages');
        $attributes = array('name' => 'frmFilterdoc_languages', 'id' => 'frmFilterdoc_languages');
        $f = form_open(site_url('doc_documents/filterdoc_languages'), $attributes);
        $f .= form_dropdown('langcode', $options, $id);
        $data = array('name' => 'btnFilterdoc_languages', 'class' => 'ibutton green', 'style' => 'margin-left:10px', 'value' => t('Filter'));
        $f .= form_submit($data);
        if ($this->session->userdata('sm_filter_doc_languages')) {
            $r = array('name' => 'btnReset', 'class' => 'ibutton red', 'style' => 'margin-left:10px', 'value' => 'Reset');  
            $f .= form_submit($r); 
        }
        $f .= form_close();
        return $f;
    }
    
    
    public function dataEdit($id)
    {
        if (!$this->_checkdoc_documentsById($id)) {
            return redirect(site_url('doc_documents'));   
        }
        $this->load->model('Doc_Tags_Model');
        $tagModel = new Doc_Tags_Model();
        $this->load->helper('editor');
        $query = $this->db->get_where('doc_documents',array('id' => $id));
        $curTags = $tagModel->getTagbyDoc($id);
        return array('query' => $query,
                    'tags' => $tagModel->getAllList(),
                    'curTags' => $curTags,
                    'cbo_doc_categories' => $this->cbo_doc_categories($query->row('cid')), 
                    'cbo_doc_languages' => $this->cbo_doc_languages($query->row('langcode'))
                    );
    }
    
    public function dataAdd()
    {
        $this->load->model('Doc_Tags_Model');
        $tagModel = new Doc_Tags_Model();
        $this->load->helper('editor');
        return array(
            'tags' => $tagModel->getAllList(),
            'cbo_doc_categories' => $this->cbo_doc_categories(), 
            'cbo_doc_languages' => $this->cbo_doc_languages());
    }
    
    public function checkForm()
    {
        $this->form_validation->set_rules('title', t('title'),'trim|required');
        $this->form_validation->set_rules('image', t('image'),'trim|required');
        $this->form_validation->set_rules('summary', t('summary'),'trim|required');
        $this->form_validation->set_rules('swf_file', t('swf_file'),'trim|required');
        $this->form_validation->set_rules('doc_file', t('doc_file'),'trim|required');
        $this->form_validation->set_rules('created', t('created'),'trim|required');
        $this->form_validation->set_rules('status', t('status'),'trim');
        $this->form_validation->set_rules('keyword', t('keyword'),'trim');
        $this->form_validation->set_rules('description', t('description'),'trim');
        $this->form_validation->set_rules('cid', t('cid'),'trim|required|integer');
        $this->form_validation->set_rules('langcode', t('langcode'),'trim|required');
        $this->form_validation->set_rules('numpage', t('numpage'),'trim|required|integer');
        $this->form_validation->set_rules('tags', t('Tags'),'trim');
        return $this->form_validation->run();
    } 
    
    private function optTags($strTag)
    {
        $arrTags = explode(',', $strTag);
        $arr = array();
        foreach ($arrTags as $tag) {
            $tag = trim($tag);
            if ($tag != '') {
                $arr[] = strtolower($tag);
            }    
        }
        return $arr;
    }
    
    private function listTags()
    {
        $this->load->model('Doc_Tags_Model');
        $tagModel = new Doc_Tags_Model();
        $tags = $tagModel->getAllList();
        $arr = array();
        foreach ($tags as $tag) {
            $arr[] = $tag->tags;
        }
        return $arr;
    }
    
    private function delTags($arrTag)
    {
        $this->load->model('Doc_Tags_Model');
        $tagModel = new Doc_Tags_Model();
        $arr = array();
        foreach ($arrTag as $ar) {
            $arr[] = "'".$ar."'";
        }
        $str = implode(',', $arr);
        $tagModel->tagsRemoved($str);
    }
    
    public function doUpdate()
    {
        
        /*Add new tag*/
        $this->load->model('Doc_Tags_Model');
        $this->load->model('Doc_Tags_Doc_Model');
        
        $tagModel = new Doc_Tags_Model();
        $tags = trim($this->input->post('tags'));
        $arrTag = $this->optTags($tags);
        $listTags = $this->listTags();
        $new_tags = array_diff($arrTag, $listTags);
        $tagModel->addnewtags($new_tags);
        
        $this->load->helper('text');
        $title = $this->input->post('title');
        $time = strtotime($this->input->post('created'));
        $this->setTitle($title);
        //$this->setAlias(iurl_title($title).'-'.date('y-m-d', $time));
        $alias = isset($_POST['alias']) ? $_POST['alias'] : iurl_title($title).'-'.date('y-m-d', $time);
        $this->setAlias($alias);
        $this->setImage($this->input->post('image'));
        $this->setSummary($this->input->post('summary'));
        $this->setSwf_file($this->input->post('swf_file'));
        $this->setDoc_file($this->input->post('doc_file'));
        $this->setCreated($time);
        $this->setStatus($this->input->post('status'));
        $this->setKeyword($this->input->post('keyword'));
        $this->setDescription($this->input->post('description'));
        $this->setCid($this->input->post('cid'));
        $this->setLangcode($this->input->post('langcode'));
        $this->setNumpage($this->input->post('numpage'));
            
        if (isset($_POST['idx'])) {
            $id = decrypt($_POST['idx']);
            $this->setId($id);
            $this->setChanged(time());
            $this->save();
        }
        if (isset($id)) {
            $docId = $id;
        } else {
            $docId = $this->save();    
        }
        
       
        if ($docId) {
            $dtModel = new Doc_Tags_Doc_Model();
            if (!empty($arrTag)) {
                $arrT = $tagModel->tagsIdList($arrTag);
                $dtModel->updateTags($docId, $arrT);    
            }            
            
            return true;
        }
        return false;
    }
    
}